package com.ygswglxt.attendance.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ygswglxt.attendance.entity.AttendanceRecord;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface AttendanceMapper extends BaseMapper<AttendanceRecord> {
    
    /**
     * 获取待审批的加班申请（联表查询员工名称）
     */
    @Select("SELECT ar.*, COALESCE(u.name, u.username) as userName " +
            "FROM attendance_record ar " +
            "LEFT JOIN sys_user u ON ar.user_id = u.id " +
            "WHERE ar.type = 'OVERTIME' AND ar.status = 'PENDING' " +
            "ORDER BY ar.create_time DESC")
    List<AttendanceRecord> selectPendingOvertimeWithUserName();
    
    /**
     * 获取下属考勤记录（联表查询员工名称）
     */
    @Select("SELECT ar.*, COALESCE(u.name, u.username) as userName " +
            "FROM attendance_record ar " +
            "LEFT JOIN sys_user u ON ar.user_id = u.id " +
            "ORDER BY ar.create_time DESC")
    List<AttendanceRecord> selectSubordinateRecordsWithUserName();
    
    /**
     * 统计指定月份的打卡记录（按用户分组）
     */
    @Select("SELECT ar.user_id, COUNT(*) as checkInCount " +
            "FROM attendance_record ar " +
            "WHERE ar.type IN ('CHECK_IN', 'CHECK_OUT') " +
            "AND DATE_FORMAT(ar.create_time, '%Y-%m') = #{month} " +
            "GROUP BY ar.user_id")
    List<Map<String, Object>> selectCheckInCountByMonth(String month);
    
    /**
     * 统计指定月份的加班记录（按用户分组）
     */
    @Select("SELECT ar.user_id, COUNT(*) as overtimeCount, " +
            "COALESCE(SUM(ar.hours), 0) as totalHours " +
            "FROM attendance_record ar " +
            "WHERE ar.type = 'OVERTIME' " +
            "AND ar.status = 'APPROVED' " +
            "AND DATE_FORMAT(ar.create_time, '%Y-%m') = #{month} " +
            "GROUP BY ar.user_id")
    List<Map<String, Object>> selectOvertimeByMonth(String month);
    
    /**
     * 统计指定月份的请假天数（按用户分组，从leave_record表查询）
     */
    @Select("SELECT lr.user_id, COALESCE(SUM(lr.days), 0) as totalDays " +
            "FROM leave_record lr " +
            "WHERE lr.status = 'APPROVED' " +
            "AND DATE_FORMAT(lr.start_date, '%Y-%m') = #{month} " +
            "GROUP BY lr.user_id")
    List<Map<String, Object>> selectLeaveDaysByMonth(String month);
    
    /**
     * 统计指定月份有请假记录的用户数
     */
    @Select("SELECT COUNT(DISTINCT lr.user_id) as leaveUserCount " +
            "FROM leave_record lr " +
            "WHERE lr.status = 'APPROVED' " +
            "AND DATE_FORMAT(lr.start_date, '%Y-%m') = #{month}")
    Long selectLeaveUserCountByMonth(String month);
    
    /**
     * 获取异常考勤记录（联表查询员工名称）
     * 支持动态条件：userId, month, exceptionType, exceptionStatus
     */
    @Select("<script>" +
            "SELECT ar.*, COALESCE(u.name, u.username) as userName " +
            "FROM attendance_record ar " +
            "LEFT JOIN sys_user u ON ar.user_id = u.id " +
            "WHERE (ar.is_late = 1 OR ar.is_early_leave = 1 OR ar.is_absent = 1) " +
            "<if test='userId != null'>" +
            "AND ar.user_id = #{userId} " +
            "</if>" +
            "<if test='month != null and month != \"\"'>" +
            "AND DATE_FORMAT(ar.attendance_date, '%Y-%m') = #{month} " +
            "</if>" +
            "<if test='exceptionType != null and exceptionType != \"\"'>" +
            "<choose>" +
            "<when test='exceptionType == \"LATE\"'>" +
            "AND ar.is_late = 1 " +
            "</when>" +
            "<when test='exceptionType == \"EARLY_LEAVE\"'>" +
            "AND ar.is_early_leave = 1 " +
            "</when>" +
            "<when test='exceptionType == \"ABSENT\"'>" +
            "AND ar.is_absent = 1 " +
            "</when>" +
            "</choose>" +
            "</if>" +
            "<if test='exceptionStatus != null and exceptionStatus != \"\"'>" +
            "AND ar.exception_status = #{exceptionStatus} " +
            "</if>" +
            "ORDER BY ar.attendance_date DESC, ar.create_time DESC" +
            "</script>")
    List<AttendanceRecord> selectExceptionRecordsWithUserName(
            @Param("userId") Long userId, 
            @Param("month") String month, 
            @Param("exceptionType") String exceptionType, 
            @Param("exceptionStatus") String exceptionStatus);
    
    /**
     * 统计指定月份每天的打卡次数（用于趋势图）
     */
    @Select("SELECT DATE_FORMAT(COALESCE(attendance_date, create_time), '%Y-%m-%d') as date, " +
            "COUNT(*) as checkIns " +
            "FROM attendance_record " +
            "WHERE type IN ('CHECK_IN', 'CHECK_OUT') " +
            "AND DATE_FORMAT(COALESCE(attendance_date, create_time), '%Y-%m') = #{month} " +
            "GROUP BY DATE_FORMAT(COALESCE(attendance_date, create_time), '%Y-%m-%d') " +
            "ORDER BY date ASC")
    List<Map<String, Object>> selectDailyCheckInsByMonth(@Param("month") String month);
}

